<?php

/**
 * class provides functions for the access to va_stratlab database
 *
 * @author voellenk
 */
class db {
  private $connection=NULL;
  private $result=NULL;
  private $dbhost;
  private $dbname;
  private $dbuser;
  private $dbpassword;
  public $error;

  public function __construct($host, $user, $pass) {
    $this->dbhost = $host;
    $this->dbuser = $user;
    $this->dbpassword = $pass;
  }
  
  # connect to database va_stratlab
  public function connect($name="") {
    if (!empty($name)) {
      $this->dbname = $name;
    }
    $this->connection = mysql_connect($this->dbhost, $this->dbuser, $this->dbpassword);
    if (!$this->connection) {
      echo ("could not connect to database.".mysql_error()."\n");
      return false;
    }
    if (!mysql_select_db($this->dbname, $this->connection)) {
      echo ("could not select database $name".mysql_error()."\n");
      return false;
    }
    return true;
  }
  
  public function query($query) {
    if (is_resource($this->connection)) {
      if (is_resource($this->result)) {
        mysql_free_result($this->result);
      }
      $this->result = @mysql_query($query, $this->connection);
      if (!$this->result) {
        $this->error="query error: ".mysql_error();
        return false;
      }
    } else {
      $this->error= "no connection to database.\n";
      return false;
    }
    return true;
  }
  
  public function fetchRow() {
    if (is_resource($this->result)) {
      $row = mysql_fetch_assoc($this->result);
      if (is_array($row)) {
        return $row;
      } else {
        return FALSE;
      }
    }
  }
  
  public function insert($sql) {
    $result = mysql_query($sql);
    $this->error = "insert error: ".mysql_error();
    return ($result);
  }
  
  public function disconnect() {
    if (is_resource($this->connection)) {
      mysql_close($this->connection);
    }
  }
  
  public function get_exchanges() {
    $sql="SELECT `id`,`name` FROM `exchange` WHERE 1";
    $this->query($sql);
    $arr=array();
    while ($row=$this->fetchRow()) {
      if ($row) $arr[]=$row;
    }
    return $arr;
  }
  
  public function get_exchange($stockname_id) {
    $sql="SELECT exchange.id, exchange.name 
      FROM `stockname` JOIN `exchange` ON (stockname.exchange_id=exchange.id) 
      WHERE stockname.id =$stockname_id";
    $this->query($sql);
    return ($this->fetchRow());
  }
  
  public function get_exchange_id($exchange) {
    $sql= "SELECT `id` FROM `exchange` WHERE `name` = '$exchange'";
    $this->query($sql);
    return ($this->fetchRow());    
  }
  
  public function get_groups() {
    $sql="SELECT id, code, name FROM groupname WHERE 1";
    $this->query($sql);
    $arr=array();
    while ($row=$this->fetchRow()) {
      if ($row) $arr[]=$row;
    }
    return $arr;
  }
  
  public function get_group($group_id) {
    $sql="SELECT id, code, name FROM groupname WHERE id=$group_id";
    $this->query($sql);
    return ($this->fetchRow());
  }
  
  public function get_price($id, $date) {
    $sql="SELECT * FROM `stockprice` WHERE `stock_id`=$id AND `date` = '$date'";
    $this->query($sql);
    return ($this->fetchRow());    
  }
  
  public function insert_price($id, $date, $o, $h, $l, $c, $vol, $oi="NULL") {
    $sql="INSERT INTO `va_stratlab`.`stockprice` (`stock_id`, `date`, `open`, `high`, `low`, `close`, `volume`, `oi`) 
          VALUES ('$id', '$date', '$o', '$h', '$l', '$c', '$vol', $oi);";
    return ($this->insert($sql));  //true on success    
  }
  
  public function update_price($id, $date, $o, $h, $l, $c, $vol, $oi="NULL") {
    $sql="UPDATE `va_stratlab`.`stockprice` SET 
          `open` = '$o', `high` = '$h', `low` = '$l',`close` = '$c', `volume` = '$vol', `oi` = '$oi'
          WHERE `stockprice`.`stock_id`= $id AND `stockprice`.`date` = '$date';";
    return $this->query($sql);
  }
  
  public function insert_symbol($symbol, $name, $exchangeId) {
    $sql= "INSERT INTO `va_stratlab`.`stockname` (`id` ,`name` ,`code` ,`exchange_id`)
           VALUES (NULL , '$name', '$symbol', '$exchangeId');";
    return ($this->insert($sql));  //true on success
  }
  
  public function get_group_symbols($group_id) {
    $sql="SELECT stockname.id AS sid, 
      stockname.code, stockname.name, 
      `from`,`to`,
      exchange.id AS eid,
      exchange.name AS ename
      FROM `groupmap` 
      JOIN stockname on (groupmap.stock_id=stockname.id) 
      JOIN exchange on (stockname.exchange_id=exchange.id)
      WHERE group_id=$group_id
      ORDER BY stockname.code";
    $this->query($sql);
    $arr=array();
    while ($row=$this->fetchRow()) {
      if ($row) $arr[]=$row;
    }
    return $arr;
  }
  
  public function get_symbol($symbol, $exchange="") {
    if ($exchange !="") {
      $sql="SELECT stockname.id, stockname.name 
        FROM stockname JOIN exchange ON (stockname.exchange_id= exchange.id)
        WHERE exchange.name='$exchange' AND stockname.code='$symbol'";
    } else {
      $sql="SELECT id, name
        FROM `stockname`
        WHERE `code` = '$symbol'";
    }
    $this->query($sql);
    $arr=array();
    while ($row=$this->fetchRow()) {
      if ($row) $arr[]=$row;
    }
    return $arr;    
  }
  
  public function get_symbols($exchangename) {
    $sql="SELECT stockname.id, stockname.code, stockname.name 
      FROM stockname JOIN exchange ON (stockname.exchange_id= exchange.id)
      WHERE exchange.name='$exchangename'
      ORDER BY stockname.code";
    $this->query($sql);
    $arr=array();
    while ($row=$this->fetchRow()) {
      if ($row) $arr[]=$row;
    }
    return $arr;
  }
  
  public function count_symbols($exchangename) {
    $sql= "SELECT COUNT(*)
      FROM stockname JOIN exchange 
      ON (stockname.exchange_id= exchange.id)
      WHERE exchange.name='$exchangename'";
    $this->query($sql);
    return ($this->fetchRow());
  }
  
  public function count_groups($groupid) {
    $sql= "SELECT COUNT( * )
      FROM groupmap
      WHERE group_id =$groupid";
    $this->query($sql);
    return ($this->fetchRow());
  } 
  
  public function get_date_boundaries($id) {
    $sql ="SELECT date FROM stockprice 
      WHERE stock_id = $id
      ORDER BY date ASC LIMIT 1";
    $this->query($sql);
    $rowfrom=$this->fetchRow();
    $sql ="SELECT date FROM stockprice 
      WHERE stock_id = $id
      ORDER BY date DESC LIMIT 1";
    $this->query($sql);
    $rowto=$this->fetchRow();    
    return (array($rowfrom["date"], $rowto["date"]));
  }
  
  public function count_observations($id) {
    $sql ="SELECT COUNT( * )
      FROM stockprice
      WHERE stock_id = $id";
    $this->query($sql);
    return ($this->fetchRow());
  }
  
  public function insert_id_into_group($id, $gid, $from, $to) {
    if ($from) {
      $f[0]=", `from`";
      $f[1]=", '$from'";
    } else {
      $f[0]="";
      $f[1]="";
    }
    if ($to) {
      $t[0]=", `to`";
      $t[1]=", '$to'";
    } else {
      $t[0]="";
      $t[1]="";
    }
    $sql ="INSERT INTO `va_stratlab`.`groupmap` 
      (`group_id`, `stock_id` $f[0] $t[0]) 
      VALUES ('$gid', '$id' $f[1] $t[1])";
    return ($this->query($sql));  //true on success
  }
  
  public function insert_group($gcode, $gname) {
    $sql ="INSERT INTO `va_stratlab`.`groupname` (`code` ,`name`)
      VALUES ('$gcode', '$gname')";
    return ($this->query($sql));  //true on success
  }
  
  public function delete_id_out_of_group($id, $gid) {
    $sql ="DELETE FROM `va_stratlab`.`groupmap` 
      WHERE `groupmap`.`group_id` = $gid AND `groupmap`.`stock_id` = $id";
    return ($this->query($sql));  //true on success    
  }
  
  public function delete_group($gid) {
    $sql ="DELETE FROM `va_stratlab`.`groupname` WHERE `groupname`.`id` = $gid";
    return ($this->query($sql));  //true on success        
  }
  
  public function update_id_in_group($id, $gid, $from, $to) {
    if ($from) $from="'".$from."'"; else $from="NULL";
    if ($to) $to="'".$to."'"; else $to="NULL";
    $sql ="UPDATE `va_stratlab`.`groupmap` 
      SET `from` = $from, `to` = $to
      WHERE `groupmap`.`group_id` =$gid AND `groupmap`.`stock_id` =$id";
    return ($this->query($sql));  //true on success    
  }
  
  public function update_group($gid, $gcode, $gname) {
    $sql="UPDATE `va_stratlab`.`groupname` 
      SET `code` = '$gcode', `name` = '$gname' 
      WHERE `groupname`.`id` =$gid;";
    return ($this->query($sql));  //true on success          
  }
  
  // logging functions
  // $connect must be either connect, disconnect, download or insertToDb
  public function insert_retriever_logentry($action="NULL", $error=false, $filename="", $filesize=0, $remark="") {
    if (!$error) $err=0; else $err=1;
    $sql="INSERT INTO `va_stratlab`.`log` 
          (`id`,`datetime`,`action`,`error`,`file`,`filesize`,`remark`) VALUES 
          (NULL, NOW( ), '$action', '$err', '$filename', '$filesize', '$remark')";
    return ($this->insert($sql)); //true on success
  }
  
  public function insert_connection_logentry($action, $error, $remark) {
    if (!$error) $err=0; else $err=1;
    $sql="INSERT INTO `va_stratlab`.`log` 
          (`id`,`datetime`,`action`,`error`, `remark`) VALUES 
          (NULL, NOW( ), '$action', '$err', '$remark')";
    return ($this->insert($sql)); //true on success
  }
  
  public function insert_DbWriter_logentry($action, $error, $filename, $i_prices, $u_prices, $newsymbols, $remark) {
    if (!$error) $err=0; else $err=1;
    $sql="INSERT INTO `va_stratlab`.`log` 
          (`id`,`datetime`,`action`,`error`,`file`,`i_prices`,`u_prices`,`newsymbols`,`remark`) VALUES 
          (NULL, NOW( ), '$action', '$err', '$filename', '$i_prices', '$u_prices', '$newsymbols', '$remark')";
    return ($this->insert($sql)); //true on success
  }
  
  public function query_dailylog($day) {
    $sql="SELECT * FROM `va_stratlab`.`log`
          WHERE `datetime` >= '$day 00:00:00'
          AND `datetime` <= '$day 23:59:59'";
    if ($this->query($sql)) {
      $arr=array();
      while ($row=$this->fetchRow()) {
        if ($row) $arr[]=$row;
      }     
      return $arr;
    } else {
      return false;
    }
  }
  
}

?>
